package br.com.funaction.eventtcontrollerv2.model;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import br.com.funaction.eventtcontrollerv2.entities.Anunciante;
import br.com.funaction.eventtcontrollerv2.entities.PDV;
import br.com.funaction.eventtcontrollerv2.util.ConexaoUtil;

public class PDVDAO implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private final String SELECT_ALL = "SELECT * FROM funactio_eventt_controller.pdv " + 
			"WHERE ID_FRANQUIA=?;";
	
	private final String SELECT_ALL_JOIN = "Select " +
	  "pdv.ID_PDV, " +
	  "pdv.NOME_PDV, " +
	  "pdv.ENDERECO_PDV, " +
	  "gold_premium.TIPO, " +
	  "condicao_remuneracao.CONDICAO, " +
	  "pdv.DATA_TERMINO_CONTRATO " +
	  "From " +
	  "funactio_eventt_controller.pdv Inner Join " +
	  "gold_premium On pdv.GOLD_PREMIUM = gold_premium.ID Inner Join " +
	  "condicao_remuneracao On pdv.COND_REMUN = condicao_remuneracao.ID " + 
	  "WHERE ID_FRANQUIA=?;";
	
	private final String INSERT_PF = "INSERT INTO funactio_eventt_controller.pdv (NOME_PDV, " + 
			"ENDERECO_PDV, " + 
			"CEP_PDV, " +
			"TEL_PDV, " + 
			"CONTATO_PDV, " + 
			"CPF_PDV, " + 
			"RG_PDV, " + 
			"DATA_INICIO_CONTRATO, " +
			"DATA_TERMINO_CONTRATO, " +
			"DIA_VENCIMENTO, " +
			"PF_PJ, " + 
			"GOLD_PREMIUM, " + 
			"COND_REMUN, " + 
			"QTD_ANUNCIO_ALOCADO, " + 
			"ID_FRANQUIA) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	
	private final String UPDATE_PF = "UPDATE funactio_eventt_controller.pdv SET NOME_PDV= ?, " +
			"ENDERECO_PDV = ?, " +
			"CEP_PDV = ?, " +
			"TEL_PDV = ?, " +
			"CONTATO_PDV = ?, " +
			"CPF_PDV = ?, " +
			"RG_PDV = ?, " +
			"DATA_INICIO_CONTRATO = ?, " +
			"DATA_TERMINO_CONTRATO = ?, " +
			"DIA_VENCIMENTO = ?, " +
			"PF_PJ = ?, " +
			"GOLD_PREMIUM = ?, " +
			"COND_REMUN = ?, " +
			"ID_FRANQUIA = ? " +
			"WHERE ID_PDV = ?;";
	
	private final String INSERT_PJ = "INSERT INTO funactio_eventt_controller.pdv " + 
			"(RAZAO_SOCIAL_PDV, " + 
			"NOME_PDV, " + 
			"ENDERECO_PDV, " + 
			"CEP_PDV, " +
			"TEL_PDV, " + 
			"CONTATO_PDV, " + 
			"CNPJ_PDV, " + 
			"DATA_INICIO_CONTRATO, " +
			"DATA_TERMINO_CONTRATO, " +
			"DIA_VENCIMENTO, " +
			"PF_PJ, " + 
			"GOLD_PREMIUM, " + 
			"COND_REMUN, " + 
			"QTD_ANUNCIO_ALOCADO, " + 
			"ID_FRANQUIA) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	
	private final String UPDATE_PJ = "UPDATE funactio_eventt_controller.pdv SET NOME_PDV= ?, " +
			"ENDERECO_PDV = ?, " +
			"CEP_PDV = ?, " +
			"TEL_PDV = ?, " +
			"CONTATO_PDV = ?, " +
			"RAZAO_SOCIAL_PDV = ?, " +
			"CNPJ_PDV = ?, " +
			"DATA_INICIO_CONTRATO = ?, " +
			"DATA_TERMINO_CONTRATO = ?, " +
			"DIA_VENCIMENTO = ?, " +
			"PF_PJ = ?, " +
			"GOLD_PREMIUM = ?, " +
			"COND_REMUN = ?, " +
			"ID_FRANQUIA = ? " +
			"WHERE ID_PDV = ?;";
	
	private final String SELECT_ALL_ID = "Select " + 
			  "pdv.NOME_PDV, " + 
			  "pdv.ENDERECO_PDV, " + 
			  "pdv.CEP_PDV, " + 
			  "pdv.TEL_PDV, " + 
			  "pdv.CONTATO_PDV, " + 
			  "pdv.CPF_PDV, " + 
			  "pdv.RG_PDV, " + 
			  "pdv.RAZAO_SOCIAL_PDV, " + 
			  "pdv.CNPJ_PDV, " + 
			  "pdv.DATA_INICIO_CONTRATO, " + 
			  "pdv.DATA_TERMINO_CONTRATO, " + 
			  "pdv.DIA_VENCIMENTO, " + 
			  "pdv.COND_REMUN, " + 
			  "pdv.QTD_ANUNCIO_ALOCADO, " + 
			  "pf_pj.TIPO As PF_PJ, " + 
			  "gold_premium.TIPO As GOLD_PREMIUM, " + 
			  "condicao_remuneracao.CONDICAO " + 
			"From " + 
			  "pdv Inner Join " + 
			  "pf_pj On pdv.PF_PJ = pf_pj.ID Inner Join " + 
			  "gold_premium On pdv.GOLD_PREMIUM = gold_premium.ID Inner Join " + 
			  "condicao_remuneracao On pdv.COND_REMUN = condicao_remuneracao.ID " + 
			"Where " + 
			  "pdv.ID_PDV = ?";
	
	private final String INSERT_REMUNERACAO = "INSERT INTO funactio_eventt_controller." + 
			"remuneracao (ID_CONDICAO_REMUNERACAO, VALOR, ID_PDV) VALUES(" + 
			"?, ?, ?);";
	
	private final String RETORNA_ID = "SELECT ID_PDV FROM funactio_eventt_controller.pdv " + 
			"ORDER BY ID_PDV DESC LIMIT 1;";
	
	private final String ALOCA_ANUNCIO = "UPDATE funactio_eventt_controller.pdv SET QTD_ANUNCIO_ALOCADO=QTD_ANUNCIO_ALOCADO+1 WHERE ID_PDV=?;";
	
	private final String SELECT_REMUNERACAO = "SELECT * FROM funactio_eventt_controller.remuneracao WHERE ID_PDV=?;";
	
	private final String SELECT_NOME = "SELECT NOME_PDV FROM funactio_eventt_controller.pdv WHERE ID_PDV=?;";
	
	private final String SELECT_VISAO_GERAL = "SELECT ID_PDV, NOME_PDV, QTD_ANUNCIO_ALOCADO FROM funactio_eventt_controller.pdv;";
	
	Connection con = null;
	PreparedStatement prepStmt = null;
	ResultSet rs = null;
	int qtdAnuncioAlocadoInicial = 0;

	public List<PDV> listaTodosPDV(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(SELECT_ALL);
		prepStmt.setInt(1, pdv.getIdFranquia());
		rs = prepStmt.executeQuery();
		
		List<PDV> resultadoBusca = new ArrayList<PDV>();
		
		while(rs.next()){
			
			PDV pdvBusca = new PDV();
			pdvBusca.setId(rs.getInt("ID_PDV"));
			pdvBusca.setNome(rs.getString("NOME_PDV"));
			resultadoBusca.add(pdvBusca);
		}
		
		ConexaoUtil.closeAll(con, prepStmt, rs);
		
		return resultadoBusca;
	}
	
	public List<PDV> listaTodosPDVVisualizar(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(SELECT_ALL_JOIN);
		prepStmt.setInt(1, pdv.getIdFranquia());
		rs = prepStmt.executeQuery();
		
		List<PDV> resultadoBusca = new ArrayList<PDV>();
		
		while(rs.next()){
			
			PDV pdvBusca = new PDV();
			pdvBusca.setId(rs.getInt("ID_PDV"));
			pdvBusca.setNome(rs.getString("NOME_PDV"));
			pdvBusca.setEndereco(rs.getString("ENDERECO_PDV"));
			pdvBusca.setGoldPremiumString(rs.getString("TIPO"));
			pdvBusca.setCondRemuneracaoString(rs.getString("CONDICAO"));
			pdvBusca.setDataTerminoContrato(rs.getDate("DATA_TERMINO_CONTRATO"));
			resultadoBusca.add(pdvBusca);
		}
		
		ConexaoUtil.closeAll(con, prepStmt, rs);
		
		return resultadoBusca;
	}
	
	public PDV listaTudoPDV(int idPDV) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(SELECT_ALL_ID);
		prepStmt.setInt(1, idPDV);
		rs = prepStmt.executeQuery();

		PDV pdv = new PDV();
		
		while(rs.next()){
			
			pdv.setNome(rs.getString("NOME_PDV"));
			pdv.setEndereco(rs.getString("ENDERECO_PDV"));
			pdv.setCep(rs.getString("CEP_PDV"));
			pdv.setTelefone(rs.getString("TEL_PDV"));
			pdv.setContato(rs.getString("CONTATO_PDV"));
			pdv.setCpf(rs.getString("CPF_PDV"));
			pdv.setRg(rs.getString("RG_PDV"));
			pdv.setRazaoSocial(rs.getString("RAZAO_SOCIAL_PDV"));
			pdv.setCnpj(rs.getString("CNPJ_PDV"));
			pdv.setDataInicioContrato(rs.getDate("DATA_INICIO_CONTRATO"));
			pdv.setDataTerminoContrato(rs.getDate("DATA_TERMINO_CONTRATO"));
			pdv.setDiaVencimento(rs.getInt("DIA_VENCIMENTO"));
			pdv.setPfPjString(rs.getString("PF_PJ"));
			pdv.setGoldPremiumString(rs.getString("GOLD_PREMIUM"));
			pdv.setCondRemuneracaoString(rs.getString("CONDICAO"));
			pdv.setCondRemuneracao(rs.getInt("COND_REMUN"));
			pdv.setQtdAnuncioAlocado(rs.getInt("QTD_ANUNCIO_ALOCADO"));
		}
		
		prepStmt = con.prepareStatement(SELECT_REMUNERACAO);
		prepStmt.setInt(1, idPDV);
		rs = prepStmt.executeQuery();
		
		List<PDV> lista = new ArrayList<PDV>();
		
		while(rs.next()){
			
			if(pdv.getCondRemuneracao()==1){
				
				pdv.setCondRemuneracaoFixo(rs.getDouble("VALOR"));
				
			} else if(pdv.getCondRemuneracao()==2){
				
				pdv.setCondRemuneracaoValorAcumulativo(rs.getDouble("VALOR"));
				
			} else {
				
				PDV pdvNovo = new PDV();
				pdvNovo.setId(rs.getInt("VALOR"));
				
				prepStmt = con.prepareStatement(SELECT_NOME);
				prepStmt.setInt(1, pdvNovo.getId());
				ResultSet rsNomePDV = prepStmt.executeQuery();
				
				while(rsNomePDV.next()){
					
					pdvNovo.setNome(rsNomePDV.getString("NOME_PDV"));
				}
				
				lista.add(pdvNovo);
			}
		}
		
		pdv.setSelecaoPDV(lista);
		
		return pdv;
	}
	
	public void persistPF(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(INSERT_PF);
		
		prepStmt.setString(1, pdv.getNome());
		prepStmt.setString(2, pdv.getEndereco());
		prepStmt.setString(3, pdv.getCep());
		prepStmt.setString(4, pdv.getTelefone());
		prepStmt.setString(5, pdv.getContato());
		prepStmt.setString(6, pdv.getCpf());
		prepStmt.setString(7, pdv.getRg());
		prepStmt.setDate(8, new java.sql.Date(pdv.getDataInicioContrato().getTime()));
		prepStmt.setDate(9, new java.sql.Date(pdv.getDataTerminoContrato().getTime()));
		prepStmt.setInt(10, pdv.getDiaVencimento());
		prepStmt.setInt(11, pdv.getPfPj());
		prepStmt.setInt(12, pdv.getGoldPremium());
		prepStmt.setInt(13, pdv.getCondRemuneracao());
		prepStmt.setInt(14, qtdAnuncioAlocadoInicial);
		prepStmt.setInt(15, pdv.getIdFranquia());
		
		prepStmt.executeUpdate();
		
		prepStmt = con.prepareStatement(RETORNA_ID);
		rs = prepStmt.executeQuery();
		
		while(rs.next()){
			
			pdv.setId(rs.getInt("ID_PDV"));
		}
		
		persistRemuneracao(pdv);
	}
	
	public void editPF(PDV pdv) throws SQLException{
		
		/*
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(UPDATE_PF);
		
		prepStmt.setString(1, pdv.getNome());
		prepStmt.setString(2, pdv.getEndereco());
		prepStmt.setString(3, pdv.getCep());
		prepStmt.setString(4, pdv.getTelefone());
		prepStmt.setString(5, pdv.getContato());
		prepStmt.setString(6, pdv.getCpf());
		prepStmt.setString(7, pdv.getRg());
		prepStmt.setDate(8, new java.sql.Date(pdv.getDataInicioContrato().getTime()));
		prepStmt.setDate(9, new java.sql.Date(pdv.getDataTerminoContrato().getTime()));
		prepStmt.setInt(10, pdv.getDiaVencimento());
		prepStmt.setInt(11, pdv.getPfPj());
		prepStmt.setInt(12, pdv.getGoldPremium());
		prepStmt.setInt(13, pdv.getCondRemuneracao());
		prepStmt.setInt(14, pdv.getIdFranquia());
		
		prepStmt.executeUpdate();
		
		persistRemuneracao(pdv);
		*/
	}
	
	public void persistPJ(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(INSERT_PJ);
		
		prepStmt.setString(1, pdv.getRazaoSocial());
		prepStmt.setString(2, pdv.getNome());
		prepStmt.setString(3, pdv.getEndereco());
		prepStmt.setString(4, pdv.getCep());
		prepStmt.setString(5, pdv.getTelefone());
		prepStmt.setString(6, pdv.getContato());
		prepStmt.setString(7, pdv.getCnpj());
		prepStmt.setDate(8, new java.sql.Date(pdv.getDataInicioContrato().getTime()));
		prepStmt.setDate(9, new java.sql.Date(pdv.getDataTerminoContrato().getTime()));
		prepStmt.setInt(10, pdv.getDiaVencimento());
		prepStmt.setInt(11, pdv.getPfPj());
		prepStmt.setInt(12, pdv.getGoldPremium());
		prepStmt.setInt(13, pdv.getCondRemuneracao());
		prepStmt.setInt(14, qtdAnuncioAlocadoInicial);
		prepStmt.setInt(15, pdv.getIdFranquia());;
		
		prepStmt.executeUpdate();
		
		prepStmt = con.prepareStatement(RETORNA_ID);
		rs = prepStmt.executeQuery();
		
		while(rs.next()){
			
			pdv.setId(rs.getInt("ID_PDV"));
		}
		
		persistRemuneracao(pdv);
	}
	
	public void editPJ(PDV pdv) throws SQLException{
		
		/*
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(UPDATE_PJ);
		
		prepStmt.setString(1, pdv.getRazaoSocial());
		prepStmt.setString(2, pdv.getNome());
		prepStmt.setString(3, pdv.getEndereco());
		prepStmt.setString(4, pdv.getCep());
		prepStmt.setString(5, pdv.getTelefone());
		prepStmt.setString(6, pdv.getContato());
		prepStmt.setString(7, pdv.getCnpj());
		prepStmt.setDate(8, new java.sql.Date(pdv.getDataInicioContrato().getTime()));
		prepStmt.setDate(9, new java.sql.Date(pdv.getDataTerminoContrato().getTime()));
		prepStmt.setInt(10, pdv.getDiaVencimento());
		prepStmt.setInt(11, pdv.getPfPj());
		prepStmt.setInt(12, pdv.getGoldPremium());
		prepStmt.setInt(13, pdv.getCondRemuneracao());
		prepStmt.setInt(14, pdv.getIdFranquia());
		
		prepStmt.executeUpdate();
		
		persistRemuneracao(pdv);
		*/
	}
	
	private void persistRemuneracao(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(INSERT_REMUNERACAO);
		
		if(pdv.getCondRemuneracao()==1){
			
			prepStmt.setInt(1, pdv.getCondRemuneracao());
			prepStmt.setDouble(2, pdv.getCondRemuneracaoFixo());
			prepStmt.setInt(3, pdv.getId());
			
			prepStmt.executeUpdate();
			
		} else if(pdv.getCondRemuneracao()==2){
			
			prepStmt.setInt(1, pdv.getCondRemuneracao());
			prepStmt.setDouble(2, pdv.getCondRemuneracaoValorAcumulativo());
			prepStmt.setInt(3, pdv.getId());
			
			prepStmt.executeUpdate();
			
		} else{
			
			List<PDV> listaPDV = pdv.getSelecaoPDV();
			for(PDV pdvNovo : listaPDV){
				
				prepStmt = con.prepareStatement(INSERT_REMUNERACAO);
				
				prepStmt.setInt(1, pdv.getCondRemuneracao());
				prepStmt.setDouble(2, pdvNovo.getId());
				prepStmt.setInt(3, pdv.getId());
				
				prepStmt.executeUpdate();
			}
			
			//******************************************************************
			Anunciante anunciante = new Anunciante();
			anunciante.setNome(pdv.getNome());
			anunciante.setEndereco(pdv.getEndereco());
			anunciante.setCep(pdv.getCep());
			anunciante.setTelefone(pdv.getTelefone());
			anunciante.setContato(pdv.getContato());
			anunciante.setCpf(pdv.getCpf());
			anunciante.setRg(pdv.getRg());
			anunciante.setDataInicioContrato(pdv.getDataInicioContrato());
			anunciante.setDataTerminoContrato(pdv.getDataTerminoContrato());
			anunciante.setDiaVencimento(pdv.getDiaVencimento());
			anunciante.setRazaoSocial(pdv.getRazaoSocial());
			anunciante.setCnpj(pdv.getCnpj());
			anunciante.setPfPj(pdv.getPfPj());
			anunciante.setIdFranquia(pdv.getIdFranquia());
			
			AnuncianteDAO anuncianteDAO = new AnuncianteDAO();
			
			if(anunciante.getPfPj()==1){
				
				anuncianteDAO.persistPF(anunciante, listaPDV);
				
			} else{
				
				anuncianteDAO.persistPJ(anunciante, listaPDV);
			}
			//******************************************************************
		}
	}
	
	public List<PDV> listaTodosPDVAnuncio(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(SELECT_ALL);
		prepStmt.setInt(1, pdv.getIdFranquia());
		rs = prepStmt.executeQuery();
		
		List<PDV> resultadoBusca = new ArrayList<PDV>();
		
		while(rs.next()){
			
			PDV pdvBusca = new PDV();
			pdvBusca.setId(rs.getInt("ID_PDV"));
			pdvBusca.setNome(rs.getString("NOME_PDV"));
			pdvBusca.setValorAnuncioVolatil(0);
			resultadoBusca.add(pdvBusca);
		}
		
		ConexaoUtil.closeAll(con, prepStmt, rs);
		
		return resultadoBusca;
	}
	
public List<PDV> listaTudoPDVVisaoGeral(PDV pdv) throws SQLException{
		
		con = ConexaoUtil.getConexao();
		prepStmt = con.prepareStatement(SELECT_VISAO_GERAL);
		rs = prepStmt.executeQuery();
		
		Connection con1 = null;
		PreparedStatement prepStmt1 = null;
		ResultSet rs1 = null;
		
		Connection con2 = null;
		PreparedStatement prepStmt2 = null;
		ResultSet rs2 = null;
		
		List<PDV> resultadoBusca = new ArrayList<PDV>();
		
		while(rs.next()){
			
			PDV pdvBusca = new PDV();
			pdvBusca.setId(rs.getInt("ID_PDV"));
			pdvBusca.setNome(rs.getString("NOME_PDV"));
			pdvBusca.setQtdAnuncioAlocado(rs.getInt("QTD_ANUNCIO_ALOCADO"));
			
			List<Anunciante> listaAnunciante = new ArrayList<Anunciante>();
			
			con1 = ConexaoUtil.getConexao();
			prepStmt1 = con1.prepareStatement("SELECT ID_ANUNCIANTE FROM funactio_eventt_controller.ANUNCIO WHERE ID_PDV=" + pdvBusca.getId() + "");
			rs1 = prepStmt1.executeQuery();
			
			while(rs1.next()){
				
				con2 = ConexaoUtil.getConexao();
				prepStmt2 = con2.prepareStatement("SELECT * FROM funactio_eventt_controller.ANUNCIANTE WHERE ID=" + rs1.getInt("ID_ANUNCIANTE") + "");
				rs2 = prepStmt2.executeQuery();
				
				while(rs2.next()){
					
					Anunciante anunciante = new Anunciante();
					anunciante.setNome(rs2.getString("NOME"));
					listaAnunciante.add(anunciante);
				}
			}
			
			pdvBusca.setListaAnunciante(listaAnunciante);
			resultadoBusca.add(pdvBusca);
		}
		
		ConexaoUtil.closeAll(con, prepStmt, rs);
		
		return resultadoBusca;
	}
}